--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Data Schema For Version 0.0.0.0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductDelete]
(
	@Original_ProductID Int
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[Products] 
WHERE 
	[ProductID] = @Original_ProductID ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelectByProductPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelectByProductPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_ProductSelectByProductPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]

FROM 
[Products]
WHERE

	[Products].[ProductID] IN (SELECT [ProductID] 
											FROM OpenXML(@hDoc, '//Products', 2) 
											WITH ([ProductID] char(36) 'ProductID')) 


exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelectByProductSinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelectByProductSinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductSelectByProductSinglePk]
(
	@ProductID Int
)
AS

SELECT 
	[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]

FROM
[Products]
WHERE 
[Products].[ProductID] = @ProductID 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_ProductInsert]
(
	@CategoryID Int = default,
	@UnitsOnOrder SmallInt = default,
	@UnitsInStock SmallInt = default,
	@UnitPrice Money = default,
	@SupplierID Int = default,
	@ReorderLevel SmallInt = default,
	@QuantityPerUnit NVarChar(20) = default,
	@ProductName NVarChar(40) = default,
	@ProductID Int = default,
	@Discontinued Bit = default,
	@created_date datetime,
	@created_by varchar (50),
	@modified_by varchar (50)

)
AS
SET NOCOUNT OFF;

if (@created_date IS NULL)
SET @created_date = GetDate()
IF (@ProductID < 0) SET @ProductID = NULL;
if ((@ProductID IS NULL))
BEGIN

INSERT INTO [Products]
(
[CategoryID],
[Discontinued],
[ProductName],
[QuantityPerUnit],
[ReorderLevel],
[SupplierID],
[UnitPrice],
[UnitsInStock],
[UnitsOnOrder],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@CategoryID,
	@Discontinued,
	@ProductName,
	@QuantityPerUnit,
	@ReorderLevel,
	@SupplierID,
	@UnitPrice,
	@UnitsInStock,
	@UnitsOnOrder,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);

END
ELSE
BEGIN
SET identity_insert [Products] on
INSERT INTO [Products]
(
[ProductID],
[CategoryID],
[Discontinued],
[ProductName],
[QuantityPerUnit],
[ReorderLevel],
[SupplierID],
[UnitPrice],
[UnitsInStock],
[UnitsOnOrder],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@ProductID,
	@CategoryID,
	@Discontinued,
	@ProductName,
	@QuantityPerUnit,
	@ReorderLevel,
	@SupplierID,
	@UnitPrice,
	@UnitsInStock,
	@UnitsOnOrder,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);
SET identity_insert [Products] off
END


SELECT 
	[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]

FROM
[Products]
WHERE
	[Products].[ProductID] = SCOPE_IDENTITY();
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductPagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductPagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductPagingSelect]
(
	@page int, -- page number selected by the user
	@pageSize int, -- number of items on the page
	@orderByColumn varchar(100), -- name of column to order things by
	@ascending bit, -- order column ascending or descending
	@filter varchar(100) = null, -- filter statement passed in to determine like criteria on order by column
	@count int out -- number of items in the collection
)
AS

SET NOCOUNT ON;


CREATE TABLE #tmpTable
(
[ProductID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[productid]
)
SELECT
	[Products].[productid]
FROM
[Products]
WHERE
	(@orderByColumn = 'CategoryID' and (((@filter is null) or ([Products].[CategoryID] is null)) or (@filter is not null and [Products].[CategoryID] = @filter)))
or
	(@orderByColumn = 'UnitsOnOrder' and (((@filter is null) or ([Products].[UnitsOnOrder] is null)) or (@filter is not null and [Products].[UnitsOnOrder] = @filter)))
or
	(@orderByColumn = 'UnitsInStock' and (((@filter is null) or ([Products].[UnitsInStock] is null)) or (@filter is not null and [Products].[UnitsInStock] = @filter)))
or
	(@orderByColumn = 'SupplierID' and (((@filter is null) or ([Products].[SupplierID] is null)) or (@filter is not null and [Products].[SupplierID] = @filter)))
or
	(@orderByColumn = 'ReorderLevel' and (((@filter is null) or ([Products].[ReorderLevel] is null)) or (@filter is not null and [Products].[ReorderLevel] = @filter)))
or
	(@orderByColumn = 'QuantityPerUnit' and (((@filter is null) or ([Products].[QuantityPerUnit] is null)) or (@filter is not null and [Products].[QuantityPerUnit] LIKE @filter)))
or
	(@orderByColumn = 'ProductName' and (((@filter is null) or ([Products].[ProductName] is null)) or (@filter is not null and [Products].[ProductName] LIKE @filter)))
or
	(@orderByColumn = 'ProductID' and (((@filter is null) or ([Products].[ProductID] is null)) or (@filter is not null and [Products].[ProductID] = @filter)))
or
	(@orderByColumn = 'Discontinued' and (((@filter is null) or ([Products].[Discontinued] is null)) or (@filter is not null and [Products].[Discontinued] = @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[Products]
WHERE
	(@orderByColumn = 'CategoryID' and (((@filter is null) or ([Products].[CategoryID] is null)) or (@filter is not null and [Products].[CategoryID] = @filter)))
or
	(@orderByColumn = 'UnitsOnOrder' and (((@filter is null) or ([Products].[UnitsOnOrder] is null)) or (@filter is not null and [Products].[UnitsOnOrder] = @filter)))
or
	(@orderByColumn = 'UnitsInStock' and (((@filter is null) or ([Products].[UnitsInStock] is null)) or (@filter is not null and [Products].[UnitsInStock] = @filter)))
or
	(@orderByColumn = 'SupplierID' and (((@filter is null) or ([Products].[SupplierID] is null)) or (@filter is not null and [Products].[SupplierID] = @filter)))
or
	(@orderByColumn = 'ReorderLevel' and (((@filter is null) or ([Products].[ReorderLevel] is null)) or (@filter is not null and [Products].[ReorderLevel] = @filter)))
or
	(@orderByColumn = 'QuantityPerUnit' and (((@filter is null) or ([Products].[QuantityPerUnit] is null)) or (@filter is not null and [Products].[QuantityPerUnit] LIKE @filter)))
or
	(@orderByColumn = 'ProductName' and (((@filter is null) or ([Products].[ProductName] is null)) or (@filter is not null and [Products].[ProductName] LIKE @filter)))
or
	(@orderByColumn = 'ProductID' and (((@filter is null) or ([Products].[ProductID] is null)) or (@filter is not null and [Products].[ProductID] = @filter)))
or
	(@orderByColumn = 'Discontinued' and (((@filter is null) or ([Products].[Discontinued] is null)) or (@filter is not null and [Products].[Discontinued] = @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Products] ON #tmpTable.[productid] = [PRODUCTS].[productid]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelectBySearchAnd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelectBySearchAnd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductSelectBySearchAnd]
(
	@CategoryID Int = null, 
	@UnitsOnOrder SmallInt = null, 
	@UnitsInStock SmallInt = null, 
	@SupplierID Int = null, 
	@ReorderLevel SmallInt = null, 
	@QuantityPerUnit NVarChar(20) = null, 
	@ProductName NVarChar(40) = null, 
	@ProductID Int = null, 
	@Discontinued Bit = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[ProductID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[productid]
)
SELECT
	[Products].[productid]
FROM
[Products]
WHERE
((@CategoryID is null) OR ([Products].[CategoryID] = @CategoryID)) AND
((@UnitsOnOrder is null) OR ([Products].[UnitsOnOrder] = @UnitsOnOrder)) AND
((@UnitsInStock is null) OR ([Products].[UnitsInStock] = @UnitsInStock)) AND
((@SupplierID is null) OR ([Products].[SupplierID] = @SupplierID)) AND
((@ReorderLevel is null) OR ([Products].[ReorderLevel] = @ReorderLevel)) AND
((@QuantityPerUnit is null) OR ([Products].[QuantityPerUnit] LIKE @QuantityPerUnit)) AND
((@ProductName is null) OR ([Products].[ProductName] LIKE @ProductName)) AND
((@ProductID is null) OR ([Products].[ProductID] = @ProductID)) AND
((@Discontinued is null) OR ([Products].[Discontinued] = @Discontinued))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Products].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Products].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Products].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Products].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Products].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Products].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Products].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Products].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[Products]
WHERE
((@CategoryID is null) OR ([Products].[CategoryID] = @CategoryID)) AND
((@UnitsOnOrder is null) OR ([Products].[UnitsOnOrder] = @UnitsOnOrder)) AND
((@UnitsInStock is null) OR ([Products].[UnitsInStock] = @UnitsInStock)) AND
((@SupplierID is null) OR ([Products].[SupplierID] = @SupplierID)) AND
((@ReorderLevel is null) OR ([Products].[ReorderLevel] = @ReorderLevel)) AND
((@QuantityPerUnit is null) OR ([Products].[QuantityPerUnit] LIKE @QuantityPerUnit)) AND
((@ProductName is null) OR ([Products].[ProductName] LIKE @ProductName)) AND
((@ProductID is null) OR ([Products].[ProductID] = @ProductID)) AND
((@Discontinued is null) OR ([Products].[Discontinued] = @Discontinued))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Products] ON #tmpTable.[productid] = [PRODUCTS].[productid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Products].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Products].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Products].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Products].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Products].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Products].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Products].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Products].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductUpdate]
(
	@CategoryID Int,
	@UnitsOnOrder SmallInt,
	@UnitsInStock SmallInt,
	@UnitPrice Money,
	@SupplierID Int,
	@ReorderLevel SmallInt,
	@QuantityPerUnit NVarChar(20),
	@ProductName NVarChar(40),
	@Discontinued Bit,
	@modified_by varchar (50),
	@modified_date datetime = null,
	@Original_ProductID Int,
	@Original_time_stamp timestamp

)
AS

IF (@modified_date IS NULL)
SET @modified_date = GetDate();

SET NOCOUNT OFF;
UPDATE 
[Products] 
SET
[CategoryID] = @CategoryID,
[Discontinued] = @Discontinued,
[ProductName] = @ProductName,
[QuantityPerUnit] = @QuantityPerUnit,
[ReorderLevel] = @ReorderLevel,
[SupplierID] = @SupplierID,
[UnitPrice] = @UnitPrice,
[UnitsInStock] = @UnitsInStock,
[UnitsOnOrder] = @UnitsOnOrder,
[modified_by] = @modified_by,
[modified_date] = @modified_date

WHERE
	[Products].[ProductID] = @Original_ProductID AND
	[Products].[time_stamp] = @Original_time_stamp


SELECT
	[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]
FROM 
[Products]
WHERE
	[Products].[ProductID] = @Original_ProductID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductSelectByCreatedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]

FROM
[Products]
WHERE
((([Products].[created_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Products].[created_date])) AND 
((([Products].[created_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Products].[created_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductSelectByModifiedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]

FROM
[Products]
WHERE
((([Products].[modified_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Products].[modified_date])) AND 
((([Products].[modified_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Products].[modified_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelectBySearchOr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelectBySearchOr]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductSelectBySearchOr]
(
	@CategoryID Int = null, 
	@UnitsOnOrder SmallInt = null, 
	@UnitsInStock SmallInt = null, 
	@SupplierID Int = null, 
	@ReorderLevel SmallInt = null, 
	@QuantityPerUnit NVarChar(20) = null, 
	@ProductName NVarChar(40) = null, 
	@ProductID Int = null, 
	@Discontinued Bit = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[ProductID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[productid]
)
SELECT
	[Products].[productid]
FROM
[Products]
WHERE
((@CategoryID is null) OR ([Products].[CategoryID] = @CategoryID)) OR
((@UnitsOnOrder is null) OR ([Products].[UnitsOnOrder] = @UnitsOnOrder)) OR
((@UnitsInStock is null) OR ([Products].[UnitsInStock] = @UnitsInStock)) OR
((@SupplierID is null) OR ([Products].[SupplierID] = @SupplierID)) OR
((@ReorderLevel is null) OR ([Products].[ReorderLevel] = @ReorderLevel)) OR
((@QuantityPerUnit is null) OR ([Products].[QuantityPerUnit] LIKE @QuantityPerUnit)) OR
((@ProductName is null) OR ([Products].[ProductName] LIKE @ProductName)) OR
((@ProductID is null) OR ([Products].[ProductID] = @ProductID)) OR
((@Discontinued is null) OR ([Products].[Discontinued] = @Discontinued))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Products].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Products].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Products].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Products].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Products].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Products].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Products].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Products].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[Products]
WHERE
((@CategoryID is null) OR ([Products].[CategoryID] = @CategoryID)) OR
((@UnitsOnOrder is null) OR ([Products].[UnitsOnOrder] = @UnitsOnOrder)) OR
((@UnitsInStock is null) OR ([Products].[UnitsInStock] = @UnitsInStock)) OR
((@SupplierID is null) OR ([Products].[SupplierID] = @SupplierID)) OR
((@ReorderLevel is null) OR ([Products].[ReorderLevel] = @ReorderLevel)) OR
((@QuantityPerUnit is null) OR ([Products].[QuantityPerUnit] LIKE @QuantityPerUnit)) OR
((@ProductName is null) OR ([Products].[ProductName] LIKE @ProductName)) OR
((@ProductID is null) OR ([Products].[ProductID] = @ProductID)) OR
((@Discontinued is null) OR ([Products].[Discontinued] = @Discontinued))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Products] ON #tmpTable.[productid] = [PRODUCTS].[productid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'CategoryID' THEN [Products].[CategoryID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'UnitsOnOrder' THEN [Products].[UnitsOnOrder] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'UnitsInStock' THEN [Products].[UnitsInStock] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'SupplierID' THEN [Products].[SupplierID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ReorderLevel' THEN [Products].[ReorderLevel] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'QuantityPerUnit' THEN [Products].[QuantityPerUnit] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ProductName' THEN [Products].[ProductName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ProductID' THEN [Products].[ProductID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Discontinued' THEN [Products].[Discontinued] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Products].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Products].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Products].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Products].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Products].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Products].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Products].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Products].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_ProductSelect]
AS

SET NOCOUNT ON;

SELECT
		[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]

FROM 
[Products]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelectByCategoryPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelectByCategoryPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_ProductSelectByCategoryPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]

FROM 
[Products]
WHERE
	[Products].[CategoryID] IN (SELECT [CategoryID]
				FROM OpenXML(@hDoc, '//Categories', 2)
				WITH ([CategoryID] Int 'CategoryID'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_ProductSelectBySupplierPks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_ProductSelectBySupplierPks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE PROCEDURE [dbo].[gen_ProductSelectBySupplierPks]
(
	@xml ntext
)
AS

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

SELECT
		[Products].[categoryid],
	[Products].[unitsonorder],
	[Products].[unitsinstock],
	[Products].[unitprice],
	[Products].[supplierid],
	[Products].[reorderlevel],
	[Products].[quantityperunit],
	[Products].[productname],
	[Products].[productid],
	[Products].[discontinued],
	[Products].[created_by],
	[Products].[created_date],
	[Products].[modified_by],
	[Products].[modified_date],
	[Products].[time_stamp]

FROM 
[Products]
WHERE
	[Products].[SupplierID] IN (SELECT [SupplierID]
				FROM OpenXML(@hDoc, '//Suppliers', 2)
				WITH ([SupplierID] Int 'SupplierID'))

exec sp_xml_removeDocument @hDoc	

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

